#group 6 -- Kyle Bakken, Chelsea Evans, Adam Lamb
#CS 419-400
#sqlhandler.py
##### Description: This script is called by filter.py
# and the email information interacts with database
# in the appropriate way.
# Script also called by CursesCLI.py for select query

import os
import objects
import string
import re
import MySQLdb

def DBConnect():
	#database info
	localhost = "mysql.eecs.oregonstate.edu"
	username = "cs419-group6"
	password = "z5eVN6nDmYCeMzRT"
	database = "cs419-group6"

	# Open database connection
	db = MySQLdb.connect(localhost, username, password, database)
	
	return db


#handle new appointments
def DBInsertNew(app):

	db = DBConnect()

	# prepare a cursor object using cursor() method
	cursor = db.cursor()
	
	sql = "INSERT INTO Student(\
	counselor, name, email) \
	VALUES ('%s', '%s', '%s')" % \
	(app.advisor, app.advisee, app.email)

	try:
		# Execute the SQL command
		cursor.execute(sql)
		# Commit your changes in the database
		db.commit()
	except:
		# Rollback in case there is any error
		db.rollback() 

	# Prepare SQL query to INSERT a record into the database.
	sql = "INSERT INTO Appointment(\
	sid, type, dateof, start_time, end_time) \
	VALUES ((SELECT id FROM Student WHERE name = '%s'), \
	'%s', '%s', '%s', '%s')" % \
	(app.advisee, app.app_type, app.date, app.start_time, app.end_time)

	
	try:
		# Execute the SQL command
		cursor.execute(sql)
		# Commit your changes in the database
		db.commit()
	except:
		# Rollback in case there is any error
		db.rollback()


	# disconnect from server
	db.close()

##Cancellations
def DBUpdateCancel(app):

	db = DBConnect()
	
	#print app.app_type # for testing

	# prepare a cursor object using cursor() method
	cursor = db.cursor()


	sql = "SELECT Student.id, Appointment.id FROM Student\
	INNER JOIN Appointment on Appointment.sid = Student.id\
	WHERE Student.name = '%s'" % (app.advisee)

	AID = ''
	SID = ''

	try:
		# Execute the SQL command
		cursor.execute(sql)
		# Fetch all the rows in a list of lists.
		results = cursor.fetchall()

		for row in results:
			SID = row[0]
			AID = row[1]
		#print SID # for testing and error handling
	except:
		db.rollback()


	sql = "DELETE FROM Appointment\
	WHERE id = '%s'" % (AID)


	try:
		# Execute the SQL command
		cursor.execute(sql)
		# Commit your changes in the database
		db.commit()
	except:
		# Rollback in case there is any error
		db.rollback()

	sql = "DELETE FROM Student\
	WHERE id = '%s'" % (SID)


	try:
		# Execute the SQL command
		cursor.execute(sql)
		# Commit your changes in the database
		db.commit()
	except:
		# Rollback in case there is any error
		db.rollback()


	# disconnect from server
	db.close()


#handle select queries
#called by CursesCLI.py
	#MUST INCLUDE in said file: import sqlhandler
	#call function by: appointmentArray (or whatever array variable) = sqlhandler.DBSelect()
	#end of each row is marked by '$' for easy delimiting and printing on CLI end  
	#returns array with Student Name, Student Email, Date, Start Time, End Time.
def DBSelect():


	db = DBConnect()

	# prepare a cursor object using cursor() method
	cursor = db.cursor()

	#query to display appointments
	sql = "SELECT s.id, s.name, s.email, a.dateof, a.start_time, a.end_time\
	FROM Appointment a \
	INNER JOIN Student s on s.id = a.sid\
	WHERE type = 'confirm'"
	
	try:
		# Execute the SQL command
		cursor.execute(sql)
		# Fetch all the rows in a list of lists.
		results = cursor.fetchall()

		returnarray = [] 

		for row in results:
			for col in row:
				returnarray.append(str(col))
				#print str(col)
			returnarray.append('$')
			

	except:
		print "Error: unable to fetch data"


	db.close()

	## return results to be displayed by CLI
	return returnarray



	##CLI Cancellations
	#called by CursesCLI.py
	#MUST INCLUDE in said file: import sqlhandler
	#call function by: sqlhandler.CLICancel(numEntry)  
	#takes parameter of a student id specified by CLI input
def CLICancel(numEntry):

	db = DBConnect()

	# prepare a cursor object using cursor() method
	cursor = db.cursor()

	sql = "SELECT s.name, s.email, a.dateof, a.start_time, a.end_time\
	FROM Appointment a \
	INNER JOIN Student s on s.id = a.sid\
	WHERE s.id = '%s'" % (numEntry)

	try:
		# Execute the SQL command
		cursor.execute(sql)
		# Fetch all the rows in a list of lists.
		results = cursor.fetchall()

		cancelarray = [] 

		for row in results:
			for col in row:
				cancelarray.append(str(col))
				#print str(col)
			
			
	except:
		print "Error: unable to fetch data"

	return cancelarray


	# disconnect from server
	db.close()

def doCancel(numEntry):

	db = DBConnect()
	
	#print app.app_type # for testing

	# prepare a cursor object using cursor() method
	cursor = db.cursor()


	sql = "SELECT Appointment.id FROM Appointment\
	INNER JOIN Student on Student.id = Appointment.sid\
	WHERE Student.ID = '%s'" % (numEntry)


	try:
		# Execute the SQL command
		cursor.execute(sql)
		# Fetch all the rows in a list of lists.
		results = cursor.fetchall()

		for row in results:
			AID = row[0]
		#print SID # for testing and error handling
	except:
		db.rollback()


	sql = "DELETE FROM Appointment\
	WHERE id = '%s'" % (AID)


	try:
		# Execute the SQL command
		cursor.execute(sql)
		# Commit your changes in the database
		db.commit()
	except:
		# Rollback in case there is any error
		db.rollback()

	sql = "DELETE FROM Student\
	WHERE id = '%s'" % (numEntry)


	try:
		# Execute the SQL command
		cursor.execute(sql)
		# Commit your changes in the database
		db.commit()
	except:
		# Rollback in case there is any error
		db.rollback()


	# disconnect from server
	db.close()


